import sys
import os
import pandas as pd
import time
import datetime
sys.path.append(r"D:/my_work/ac_lingxing_api/ac_lingxing_api")
from database.mysql_utils import MysqlUtils
from make_file import tools, work_sheet, add_sku
from  openpyxl import  Workbook 
from tkinter import filedialog
from sqlalchemy import create_engine



class add_asin(add_sku):

    def __init__(self):
        super().__init__()
        self.keyword = MysqlUtils("keyword")
        self.engine_ad_api = MysqlUtils("ad_api")
        self.engine_base = MysqlUtils("ac_base_data")
    
    
    def get_tasks_data(self):
        """获取所有需要执行的任务数据"""
        query = "select * from advertising_tasks where status = 0"
        query_data = pd.read_sql(query, con=self.keyword.engine)
        return query_data

    def get_asin_group_data(self, asin, sid_str):
        """通过asin获取该asin所在广告组的数据"""
        query = f"select * from sp_product_ads_data_api where   DATE(update_time) = (select max(DATE(update_time)) from sp_product_ads_data_api) and asin = '{asin}' and state = 'enabled' and sid in ({sid_str})"
        query_data = pd.read_sql(query, con=self.engine_ad_api.engine)
        return query_data
    
    def get_sid(self, account, country):
        if account == "全部店铺" and country == "全部站点":
            query = f"select lx_sid from store_info"
        elif account == "全部店铺":
            query = f"select lx_sid from store_info lx_country = '{country}'"
        elif country == "全部站点":
            query = f"select lx_sid from store_info where store_name = '{account}' "
        else:
            query = f"select lx_sid from store_info where store_name = '{account}' and lx_country = '{country}'"
        query_data = pd.read_sql(query, con=self.engine_base.engine)
        return list(query_data['lx_sid'])
    
    def get_all_asin(self, sid_data, asin):
        sql  = f"select asin, seller_sku, sid from lingxing_listing where sid in ({sid_data}) and asin = '{asin}' and is_delete <> 1 and date = (select max(date) from asin) and (quantity + afn_fulfillable_quantity + reserved_fc_transfers + reserved_fc_processing + reserved_customerorders + afn_inbound_shipped_quantity + afn_inbound_receiving_quantity) > 0"
        asin_data = pd.read_sql(sql, con=self.engine_base.engine)
        return asin_data

    def ad_handler(self, ad_group_sid, asin_info):
        # 根据拼接后的广告组数据，找到广告活动数据
        ad_group_sid_dict = ad_group_sid.iloc[0].to_dict()
        # 创建广告活动信息
        self.campaign_handler(ad_group_sid_dict)
        # 创建广告组信息。
        self.ad_group_handler(ad_group_sid_dict, asin_info)
    
    def ad_group_handler(self, ad_group_sid_dict, asin_info):
        lst_ad_group = tools.get_new_lst()
        lst_ad_group[tools.Entity] = 'ad group'
        lst_ad_group[tools.CampaignId] = ad_group_sid_dict['campaign_id']
        lst_ad_group[tools.AdGroupId] = ad_group_sid_dict['ad_group_id']

        lst_ad_group[tools.State] = ad_group_sid_dict['state_x']
        sid = ad_group_sid_dict['sid']
        self.excel_account[sid].add_data(lst_ad_group)
        for i, data in asin_info.iterrows():
            lst_pro_ad = tools.get_new_lst()
            lst_pro_ad[tools.Entity] = 'product ad'
            lst_pro_ad[tools.Operation] = 'create'
            lst_pro_ad[tools.CampaignId] = ad_group_sid_dict['campaign_id']
            lst_pro_ad[tools.AdGroupId] = ad_group_sid_dict['ad_group_id']
            lst_pro_ad[tools.State] = 'enabled'
            lst_pro_ad[tools.ASIN] = data['asin']
            lst_pro_ad[tools.SKU] = data['seller_sku']
            self.excel_account[sid].add_data(lst_pro_ad)

    def make_add_inifo(self, data, campaigns_data):
        """传入一条任务数据，通过asin匹配，生成批量广告文件"""
        account = data['store']
        site = data['site']
        ori_asin = data['original_asin']
        nwe_asin = data['new_asin']
        tasks_id = data['id']
        # 通过 数据中的国家和站点获取对应的sid。返回的是sid列表
        sid_lst = self.get_sid(account, site)
        if len(sid_lst) == 0:
            return
        sid_str = ",".join(["'" + str(i) + "'" for i in sid_lst])
        # 原asin所在的广告组
        asin_group_data = self.get_asin_group_data(ori_asin, sid_str)
        # 把asin 与广告组进行merge
        asin_group_data = asin_group_data.merge(campaigns_data, how='left', on=["profile_id", "campaign_id", "sid", "account"])
        # asin_info = self.get_all_asin(sid_str, nwe_asin)
        # 首先遍历国家，数据，把各个国家的数据拆分开。
        for sid in set(asin_group_data['sid']):
            # 根据国家，找到新asin的信息。
            asin_info = self.get_all_asin("'"+str(sid)+"'", nwe_asin)
            # 如果信息为空，则直接结束这个国家。
            if len(asin_info) == 0:
                continue
            # 获取当前sid里面的广告组数据
            asin_group_data_sid = asin_group_data[asin_group_data['sid'] == sid]
            
            # 遍历该asin所在的广告组，
            for ad_group in set(asin_group_data_sid['ad_group_id']):
                print(f"处理该广告组数据:{ad_group}")
                # 获取当前某个广告组，
                asin_ad_data_oneasin = asin_group_data_sid[asin_group_data_sid['ad_group_id'] == ad_group]
                
                # 进行数据处理，传入某个广告组数据，和需要添加进去的asin数据
                self.ad_handler(asin_ad_data_oneasin, asin_info)

            self.excel_account[sid].info['tasks_id'] = tasks_id
        



        
    
    def main(self):
        # 获取所有的国家sid
        self.account_data = self.get_all_sid().dropna(how="any")
        #根据国家sid创建表格对象
        self.excel_account = self.make_excel(self.account_data)
        # 获取需要添加的信息
        tasks_data = self.get_tasks_data()
        # 获取所有的广告活动数据
        campaigns_data = self.get_campaigns_data()

        # 对需要添加的信息进行迭代
        for i, data in tasks_data.iterrows():
            # 传入每一个需要添加的数据
            self.make_add_inifo(data, campaigns_data)
        lst = []
        for work_excel in self.excel_account.values():
            if (work_excel.ws.max_row) > 1:
                lst.append(work_excel.info)
                work_excel.save_file()
        save_df = pd.DataFrame(lst)
        save_df['update_time'] = datetime.datetime.now()
        save_df['is_upload'] = 0

        save_df.to_sql("new_sku_insert", con=self.engine_rpa.engine, index=False, if_exists="append")

def main(*args):
    add_as = add_asin()
    add_as.main()

if __name__ == "__main__":

    main()